<?php
App::uses('AppController', 'Controller');
App::uses('CakeEmail', 'Network/Email');
App::import('Vendor', 'mpdf/mpdf');


/**
 * Reports Controller
 *
 * @property Report $Report
 */
class ReportsController extends AppController {

    // public $components = array('Mpdf');


/**
 * index method
 *
 * @return void
 */

    public function index() {
        if(!empty($this->request->data['ReturnChartDate']['date3'])){
            $this->redirect(array('action'=>'returncharts', $this->request->data['ReturnChartDate']['date3']));
        }
        else if (!empty($this->request->data['RPMChartDate']['date2'])){
            $this->rpmcharts($this->request->data['RPMChartDate']['date2']);
        }
        else if (!empty($this->request->data['TaxedPortfolioDate']['date1']))
        {
            $this->taxedportfoliochart($this->request->data['TaxedPortfolioDate']['date1']);
        }

    }

    private function taxedportfoliochart($date) 
    {
        include("Component/connection.php");
        $filename=$date."_Taxedportfolio.csv";
        $this->set(compact('date1')); 
        $fileLocation = getenv("WWW_ROOT") . "files/".$filename;
        $file = fopen($fileLocation,"w");
        $dateExplode=explode('-',$date);
        $year=$dateExplode[0];
        $month=$dateExplode[1];
        $day=$dateExplode[2];
        $datetext=$day." ";
        if($month=='1'){$datetext=$datetext."January ";}elseif($month=='2'){$datetext=$datetext."February ";}elseif($month=='3'){$datetext=$datetext."March ";}elseif($month=='4'){$datetext=$datetext."April ";}elseif($month=='5'){$datetext=$datetext."May ";}elseif($month=='6'){$datetext=$datetext."June ";}
            elseif($month=='7'){$datetext=$datetext."July ";}elseif($month=='8'){$datetext=$datetext."August ";}elseif($month=='9'){$datetext=$datetext."September ";}elseif($month=='10'){$datetext=$datetext."October ";}elseif($month=='11'){$datetext=$datetext."November ";}elseif($month=='12'){$datetext=$datetext."December ";}
        $datetext=$datetext.$year;


        //header
        $headerL1="Taxed Portfolio";
        $headerL2="Performance Table";
        $headerL3=",,BENCHMARK,1 Month,3 Months,6 Months,1 Year,2 Year(p.a.),3 Years(p.a.),5 Years(p.a.),Since inception(p.a.),Since inception(cum)";

        //write header
        fwrite($file,$headerL1.PHP_EOL);
        fwrite($file,$headerL2.PHP_EOL);
        fwrite($file,$headerL3.PHP_EOL);

        //start generating body(portfolios with only one index)
        $portnameArray=array("Enhanced Cash","Sovereign Bonds","Diversified Credit & Fixed Income","Global Infrastructure","Australian REITS","Global REITS","AEQ Large Cap","AEQ Small / Mid Cap","IEQ","IEQ Asia","Cash");
        $indexnameArray=array("RBA Bank accepted Bills 90 Days","UBS Composite 0+ Yr TR AUD","Barclays Global Credit Corp TR Hdg AUD","UBS G INFRA & UTIL 50-50 HEDGED AUD - TOT RETURN IND","S&P/ASX 300 A-REIT TR","UBS Global Investors NR Hdg AUD","S&P/ASX 300 TR","S&P/ASX Small Ordinaries TR AUD","MSCI World Ex Australia NR AUD","MSCI AC Asia Ex Japan NR AUD","RBA Bank accepted Bills 90 Days");

        for($n=0;$n<sizeof($portnameArray);$n++)
        {
            //generate content beginning (1 index portfolios)
            $portname=$portnameArray[$n];
            $indexname=$indexnameArray[$n];
            $partL1="";
            $partL2=$portname;
            $partL3="TOTAL ".$portname.",,";
            $partL4="INDEX,,".$indexname;
            $partL5="%Return,,";

            //get data from db and add to content
            $sql="select OneMonthReturn,ThreeMonthReturn,SixMonthReturn,OneYearReturn,TwoYearReturn,ThreeYearReturn,FiveYearReturn,SinceInceptionReturnc,SinceInceptionReturn from indices where indexname='".$indexname."' and year(date)=year('".$date."') and month(date)=month('".$date."')";
            $result=mysqli_query($con,$sql);
            foreach($result as $re)
            {
                foreach($re as $r)
                {
                    if($r == null)
                    {
                        $partL4=$partL4.",-";
                    }
                    else
                    {
                        $partL4=$partL4.",".number_format((float)$r, 8, '.', '');
                    }
                }
            }
            $sql="select OneMonthReturn,ThreeMonthReturn,SixMonthReturn,OneYearReturn,TwoYearReturn,ThreeYearReturn,FiveYearReturn,SinceInceptionReturnc,SinceInceptionReturn from modelportfolios where portfolioname='".$portname."' and year(date)=year('".$date."') and month(date)=month('".$date."')";
            $result2=mysqli_query($con,$sql);
            foreach($result2 as $re)
            {
                foreach($re as $r)
                {
                    if($r == null)
                    {
                        $partL3=$partL3.",-";
                        $partL5=$partL5.",-";
                    }
                    else
                    {
                        $partL3=$partL3.",".$r;
                        $partL5=$partL5.",".$this->convertpercent(number_format((float)$r, 8, '.', ''));
                    }
                }
            }
            //write content
            fwrite($file,$partL1.PHP_EOL);
            fwrite($file,$partL2.PHP_EOL);
            fwrite($file,$partL3.PHP_EOL);
            fwrite($file,$partL4.PHP_EOL);
            fwrite($file,$partL5.PHP_EOL);
        }

        //body (portfolios with 2 indices)
        //get data from db and add to content
        $sql="select OneMonthReturn,ThreeMonthReturn,SixMonthReturn,OneYearReturn,TwoYeaRreturn,ThreeYearReturn,FiveYearReturn,SinceInceptionReturnc,SinceInceptionReturn from indices where indexname='RBA Bank accepted Bills 90 Days' and year(date)=year('".$date."') and month(date)=month('".$date."')";
        $partL6="Index,,HFRI Macro (Total) Index (USD)";
        $partL4="INDEX,,RBA Bank accepted Bills 90 Days";
        $result=mysqli_query($con,$sql);
        foreach($result as $re)
        {
            foreach($re as $r)
            {
                if($r == null)
                {
                    $partL4=$partL4.",-";
                }
                else
                {
                    $partL4=$partL4.",".number_format((float)$r, 8, '.', '');
                }
            }
        }
        $sql2="select OneMonthReturn,ThreeMonthReturn,sixmonthreturn,OneYearReturn,twoyearreturn,ThreeYearReturn,FiveYearReturn,SinceInceptionReturnc,SinceInceptionReturn from indices where indexname='HFRI Macro (Total) Index (USD)' and year(date)=year('".$date."') and month(date)=month('".$date."')";
        $result2=mysqli_query($con,$sql2);
        foreach($result2 as $re)
        {
            foreach($re as $r)
            {
                if($r == null)
                {
                    $partL6=$partL6.",-";
                }
                else
                {
                    $partL6=$partL6.",".number_format((float)$r, 8, '.', '');
                }
            }
        }
        $sql="select OneMonthReturn,ThreeMonthReturn,SixMonthreturn,OneYearReturn,TwoYearReturn,ThreeYearReturn,FiveYearReturn,SinceInceptionReturnc,SinceInceptionReturn from modelportfolios where portfolioname='Alternative Investment Strategies (AIS)' and year(date)=year('".$date."') and month(date)=month('".$date."')";
        $result2=mysqli_query($con,$sql);
        foreach($result2 as $re)
        {
            foreach($re as $r)
            {
                if($r == null)
                {
                    $partL3=$partL3.",-";
                    $partL5=$partL5.",-";
                }
                else
                {
                    $partL3=$partL3.",".$r;
                    $partL5=$partL5.",".$this->convertpercent(number_format((float)$r, 8, '.', ''));
                }
            }
        }
        $partL2="Alternative Investment Strategies (AIS)";
        fwrite($file,$partL1.PHP_EOL);
        fwrite($file,$partL2.PHP_EOL);
        fwrite($file,$partL3.PHP_EOL);
        fwrite($file,$partL4.PHP_EOL);
        fwrite($file,$partL5.PHP_EOL);
        fwrite($file,$partL6.PHP_EOL);


        fpassthru($file);
        fclose($file);
        $this->response->file('webroot/files' . DS . $filename, array(
            'download' => true,
            'name' => $filename
        ));
    }


    public function rpmcharts($date) 
    {
        include("Component/connection.php");
        $filename=$date."_RPM_Performance.csv";
        $this->set(compact('date2')); 
        $fileLocation = getenv("WWW_ROOT") . "files/".$filename;
        $file = fopen($fileLocation,"w");
        $dateExplode=explode('-',$date);
        $year=$dateExplode[0];
        $month=$dateExplode[1];
        $day=$dateExplode[2];
        $datetext=$day." ";
        if($month=='1'){$datetext=$datetext."January ";}elseif($month=='2'){$datetext=$datetext."February ";}elseif($month=='3'){$datetext=$datetext."March ";}elseif($month=='4'){$datetext=$datetext."April ";}elseif($month=='5'){$datetext=$datetext."May ";}elseif($month=='6'){$datetext=$datetext."June ";}
            elseif($month=='7'){$datetext=$datetext."July ";}elseif($month=='8'){$datetext=$datetext."August ";}elseif($month=='9'){$datetext=$datetext."September ";}elseif($month=='10'){$datetext=$datetext."October ";}elseif($month=='11'){$datetext=$datetext."November ";}elseif($month=='12'){$datetext=$datetext."December ";}
        $datetext=$datetext.$year;
        //header of csv
        fwrite($file,",,,Period Returns".PHP_EOL);
        fwrite($file,",,For period ending ".$datetext.PHP_EOL);
        fwrite($file,"RPM,1 Month,3 Months,6 Months,1 Year,2 Years,3 Years,5 Years,Since Inception".PHP_EOL.PHP_EOL);

        //Risk Profile Model - Conservative
        $contentTAA="Conservative(TAA)";
        $contentSAA="Conservative(SAA)";
        $contentIndex="Index";
        $outTAA="Outperformance (TAA)";
        $outSAA="Outperformance (SAA)";
        $TAA=array();
        $SAA=array();
        $Index=array();
        $OutTAA=array();
        $OutSAA=array();
        $sql="select OneMonthReturn,ThreeMonthReturn,sixmonthreturn,OneYearReturn,twoyearreturn,ThreeYearReturn,FiveYearReturn,SinceInceptionReturn from modelportfolios where portfolioname='Risk Profile Model - Mod Conservative TAA' and year(date)=year('".$date."') and month(date)=month('".$date."')";
        $result=mysqli_query($con,$sql);
        foreach($result as $re)
        {
            foreach ($re as $r)
            {
                $contentTAA = $contentTAA.",".$this->convertpercent(number_format((float)$r, 4, '.', ''));
                array_push($TAA,$r);
            }
        }

        $sql="select OneMonthReturn,ThreeMonthReturn,sixmonthreturn,OneYearReturn,twoyearreturn,ThreeYearReturn,FiveYearReturn,SinceInceptionReturn from modelportfolios where portfolioname='Risk Profile Model - Mod Conservative SAA' and year(date)=year('".$date."') and month(date)=month('".$date."')";
        $result=mysqli_query($con,$sql);
        foreach($result as $re)
        {
            foreach ($re as $r)
            {
                $contentSAA = $contentSAA.",".$this->convertpercent(number_format((float)$r, 4, '.', ''));
                array_push($SAA,$r);
            }
        }

        $sql="select OneMonthReturn,ThreeMonthReturn,sixmonthreturn,OneYearReturn,twoyearreturn,ThreeYearReturn,FiveYearReturn,SinceInceptionReturn from indices where indexname='Mstar PG ITr Multisector Conserv Idx' and year(date)=year('".$date."') and month(date)=month('".$date."')";
        $result=mysqli_query($con,$sql);
        foreach($result as $re)
        {
            foreach($re as $r)
            {
                $contentIndex=$contentIndex.",".$this->convertpercent(number_format((float)$r, 4, '.', ''));
                array_push($Index,$r);
            }
        }

        for($n=0;$n<sizeof($TAA);$n++)
        {
            $r1=$Index[$n]-$TAA[$n];
            $r2=$Index[$n]-$SAA[$n];
            array_push($OutTAA,$r1);
            array_push($OutSAA,$r2);
        }
        foreach($OutTAA as $oa)
        {
            $outTAA=$outTAA.",".$oa;
        }
        foreach($OutSAA as $os)
        {
            $outSAA=$outSAA.",".$oa;
        }
        fwrite($file,$contentSAA.PHP_EOL);
        fwrite($file,$contentTAA.PHP_EOL);
        fwrite($file,$contentIndex.PHP_EOL);
        fwrite($file,$outSAA.PHP_EOL);
        fwrite($file,$outTAA.PHP_EOL);
        fwrite($file,"".PHP_EOL);

        //Risk Profile Model - Mod Conservative
        $contentTAA="Conservative(TAA)";
        $contentSAA="Conservative(SAA)";
        $contentIndex="Index";
        $outTAA="Outperformance (TAA)";
        $outSAA="Outperformance (SAA)";
        $TAA=array();
        $SAA=array();
        $Index=array();
        $OutTAA=array();
        $OutSAA=array();
        $sql="select OneMonthReturn,ThreeMonthReturn,sixmonthreturn,OneYearReturn,twoyearreturn,ThreeYearReturn,FiveYearReturn,SinceInceptionReturn from modelportfolios where portfolioname='Risk Profile Model - Mod Conservative TAA' and year(date)=year('".$date."') and month(date)=month('".$date."')";
        $result=mysqli_query($con,$sql);
        foreach($result as $re)
        {
            foreach ($re as $r)
            {
                $contentTAA = $contentTAA.",".$this->convertpercent(number_format((float)$r, 4, '.', ''));
                array_push($TAA,$r);
            }
        }

        $sql="select OneMonthReturn,ThreeMonthReturn,sixmonthreturn,OneYearReturn,twoyearreturn,ThreeYearReturn,FiveYearReturn,SinceInceptionReturn from modelportfolios where portfolioname='Risk Profile Model - Mod Conservative SAA' and year(date)=year('".$date."') and month(date)=month('".$date."')";
        $result=mysqli_query($con,$sql);
        foreach($result as $re)
        {
            foreach ($re as $r)
            {
                $contentSAA = $contentSAA.",".$this->convertpercent(number_format((float)$r, 4, '.', ''));
                array_push($SAA,$r);
            }
        }

        $sql="select OneMonthReturn,ThreeMonthReturn,sixmonthreturn,OneYearReturn,twoyearreturn,ThreeYearReturn,FiveYearReturn,SinceInceptionReturn from indices where indexname='Mstar PG ITr Multisector Moderate Idx' and year(date)=year('".$date."') and month(date)=month('".$date."')";
        $result=mysqli_query($con,$sql);
        foreach($result as $re)
        {
            foreach($re as $r)
            {
                $contentIndex=$contentIndex.",".$this->convertpercent(number_format((float)$r, 4, '.', ''));
                array_push($Index,$r);
            }
        }

        for($n=0;$n<sizeof($TAA);$n++)
        {
            $r1=$Index[$n]-$TAA[$n];
            $r2=$Index[$n]-$SAA[$n];
            array_push($OutTAA,$r1);
            array_push($OutSAA,$r2);
        }
        foreach($OutTAA as $oa)
        {
            $outTAA=$outTAA.",".$oa;
        }
        foreach($OutSAA as $os)
        {
            $outSAA=$outSAA.",".$oa;
        }
        fwrite($file,$contentSAA.PHP_EOL);
        fwrite($file,$contentTAA.PHP_EOL);
        fwrite($file,$contentIndex.PHP_EOL);
        fwrite($file,$outSAA.PHP_EOL);
        fwrite($file,$outTAA.PHP_EOL);
        fwrite($file,"".PHP_EOL);

        //Risk Profile Model - Balanced
        $contentTAA="Conservative(TAA)";
        $contentSAA="Conservative(SAA)";
        $contentIndex="Index";
        $outTAA="Outperformance (TAA)";
        $outSAA="Outperformance (SAA)";
        $TAA=array();
        $SAA=array();
        $Index=array();
        $OutTAA=array();
        $OutSAA=array();
        $sql="select OneMonthReturn,ThreeMonthReturn,sixmonthreturn,OneYearReturn,twoyearreturn,ThreeYearReturn,FiveYearReturn,SinceInceptionReturn from modelportfolios where portfolioname='RRisk Profile Model - Balanced TAA' and year(date)=year('".$date."') and month(date)=month('".$date."')";
        $result=mysqli_query($con,$sql);
        foreach($result as $re)
        {
            foreach ($re as $r)
            {
                $contentTAA = $contentTAA.",".$this->convertpercent(number_format((float)$r, 4, '.', ''));
                array_push($TAA,$r);
            }
        }

        $sql="select OneMonthReturn,ThreeMonthReturn,sixmonthreturn,OneYearReturn,twoyearreturn,ThreeYearReturn,FiveYearReturn,SinceInceptionReturn from modelportfolios where portfolioname='Risk Profile Model - Balanced SAA' and year(date)=year('".$date."') and month(date)=month('".$date."')";
        $result=mysqli_query($con,$sql);
        foreach($result as $re)
        {
            foreach ($re as $r)
            {
                $contentSAA = $contentSAA.",".$this->convertpercent(number_format((float)$r, 4, '.', ''));
                array_push($SAA,$r);
            }
        }

        $sql="select OneMonthReturn,ThreeMonthReturn,sixmonthreturn,OneYearReturn,twoyearreturn,ThreeYearReturn,FiveYearReturn,SinceInceptionReturn from indices where indexname='Mstar PG Itr Multisector Balanced Idx' and year(date)=year('".$date."') and month(date)=month('".$date."')";
        $result=mysqli_query($con,$sql);
        foreach($result as $re)
        {
            foreach($re as $r)
            {
                $contentIndex=$contentIndex.",".$this->convertpercent(number_format((float)$r, 4, '.', ''));
                array_push($Index,$r);
            }
        }

        for($n=0;$n<sizeof($TAA);$n++)
        {
            $r1=$Index[$n]-$TAA[$n];
            $r2=$Index[$n]-$SAA[$n];
            array_push($OutTAA,$r1);
            array_push($OutSAA,$r2);
        }
        foreach($OutTAA as $oa)
        {
            $outTAA=$outTAA.",".$oa;
        }
        foreach($OutSAA as $os)
        {
            $outSAA=$outSAA.",".$oa;
        }
        fwrite($file,$contentSAA.PHP_EOL);
        fwrite($file,$contentTAA.PHP_EOL);
        fwrite($file,$contentIndex.PHP_EOL);
        fwrite($file,$outSAA.PHP_EOL);
        fwrite($file,$outTAA.PHP_EOL);
        fwrite($file,"".PHP_EOL);

        //Risk Profile Model - Growth
        $contentTAA="Conservative(TAA)";
        $contentSAA="Conservative(SAA)";
        $contentIndex="Index";
        $outTAA="Outperformance (TAA)";
        $outSAA="Outperformance (SAA)";
        $TAA=array();
        $SAA=array();
        $Index=array();
        $OutTAA=array();
        $OutSAA=array();
        $sql="select OneMonthReturn,ThreeMonthReturn,sixmonthreturn,OneYearReturn,twoyearreturn,ThreeYearReturn,FiveYearReturn,SinceInceptionReturn from modelportfolios where portfolioname='Risk Profile Model - Growth TAA' and year(date)=year('".$date."') and month(date)=month('".$date."')";
        $result=mysqli_query($con,$sql);
        foreach($result as $re)
        {
            foreach ($re as $r)
            {
                $contentTAA = $contentTAA.",".$this->convertpercent(number_format((float)$r, 4, '.', ''));
                array_push($TAA,$r);
            }
        }

        $sql="select OneMonthReturn,ThreeMonthReturn,sixmonthreturn,OneYearReturn,twoyearreturn,ThreeYearReturn,FiveYearReturn,SinceInceptionReturn from modelportfolios where portfolioname='Risk Profile Model - Growth SAA' and year(date)=year('".$date."') and month(date)=month('".$date."')";
        $result=mysqli_query($con,$sql);
        foreach($result as $re)
        {
            foreach ($re as $r)
            {
                $contentSAA = $contentSAA.",".$this->convertpercent(number_format((float)$r, 4, '.', ''));
                array_push($SAA,$r);
            }
        }

        $sql="select OneMonthReturn,ThreeMonthReturn,sixmonthreturn,OneYearReturn,twoyearreturn,ThreeYearReturn,FiveYearReturn,SinceInceptionReturn from indices where indexname='Mstar PG ITr Multisector Growth Idx' and year(date)=year('".$date."') and month(date)=month('".$date."')";
        $result=mysqli_query($con,$sql);
        foreach($result as $re)
        {
            foreach($re as $r)
            {
                $contentIndex=$contentIndex.",".$this->convertpercent(number_format((float)$r, 4, '.', ''));
                array_push($Index,$r);
            }
        }

        for($n=0;$n<sizeof($TAA);$n++)
        {
            $r1=$Index[$n]-$TAA[$n];
            $r2=$Index[$n]-$SAA[$n];
            array_push($OutTAA,$r1);
            array_push($OutSAA,$r2);
        }
        foreach($OutTAA as $oa)
        {
            $outTAA=$outTAA.",".$oa;
        }
        foreach($OutSAA as $os)
        {
            $outSAA=$outSAA.",".$oa;
        }
        fwrite($file,$contentSAA.PHP_EOL);
        fwrite($file,$contentTAA.PHP_EOL);
        fwrite($file,$contentIndex.PHP_EOL);
        fwrite($file,$outSAA.PHP_EOL);
        fwrite($file,$outTAA.PHP_EOL);
        fwrite($file,"".PHP_EOL);

        //Risk Profile Model - High Growth
        $contentTAA="Conservative(TAA)";
        $contentSAA="Conservative(SAA)";
        $contentIndex="Index";
        $outTAA="Outperformance (TAA)";
        $outSAA="Outperformance (SAA)";
        $TAA=array();
        $SAA=array();
        $Index=array();
        $OutTAA=array();
        $OutSAA=array();
        $sql="select OneMonthReturn,ThreeMonthReturn,sixmonthreturn,OneYearReturn,twoyearreturn,ThreeYearReturn,FiveYearReturn,SinceInceptionReturn from modelportfolios where portfolioname='Risk Profile Model - High Growth' and year(date)=year('".$date."') and month(date)=month('".$date."')";
        $result=mysqli_query($con,$sql);
        foreach($result as $re)
        {
            foreach ($re as $r)
            {
                $contentTAA = $contentTAA.",".$this->convertpercent(number_format((float)$r, 4, '.', ''));
                array_push($TAA,$r);
            }
        }

        $sql="select OneMonthReturn,ThreeMonthReturn,sixmonthreturn,OneYearReturn,twoyearreturn,ThreeYearReturn,FiveYearReturn,SinceInceptionReturn from modelportfolios where portfolioname='Risk Profile Model - High Growth' and year(date)=year('".$date."') and month(date)=month('".$date."')";
        $result=mysqli_query($con,$sql);
        foreach($result as $re)
        {
            foreach ($re as $r)
            {
                $contentSAA = $contentSAA.",".$this->convertpercent(number_format((float)$r, 4, '.', ''));
                array_push($SAA,$r);
            }
        }

        $sql="select OneMonthReturn,ThreeMonthReturn,sixmonthreturn,OneYearReturn,twoyearreturn,ThreeYearReturn,FiveYearReturn,SinceInceptionReturn from indices where indexname='Mstar PG ITr Multisector Aggressive Idx' and year(date)=year('".$date."') and month(date)=month('".$date."')";
        $result=mysqli_query($con,$sql);
        foreach($result as $re)
        {
            foreach($re as $r)
            {
                $contentIndex=$contentIndex.",".$this->convertpercent(number_format((float)$r, 4, '.', ''));
                array_push($Index,$r);
            }
        }

        for($n=0;$n<sizeof($TAA);$n++)
        {
            $r1=$Index[$n]-$TAA[$n];
            $r2=$Index[$n]-$SAA[$n];
            array_push($OutTAA,$r1);
            array_push($OutSAA,$r2);
        }
        foreach($OutTAA as $oa)
        {
            $outTAA=$outTAA.",".$oa;
        }
        foreach($OutSAA as $os)
        {
            $outSAA=$outSAA.",".$oa;
        }
        fwrite($file,$contentSAA.PHP_EOL);
        fwrite($file,$contentTAA.PHP_EOL);
        fwrite($file,$contentIndex.PHP_EOL);
        fwrite($file,$outSAA.PHP_EOL);
        fwrite($file,$outTAA.PHP_EOL);
        fwrite($file,"".PHP_EOL);

        fpassthru($file);
        fclose($file);
        $this->response->file('webroot/files' . DS . $filename, array(
            'download' => true,
            'name' => $filename
        ));
    }

    public function returncharts($date3 = null) 
    { 
    $this->layout = 'returncharts'; //Use the custom.ctp layout 


    $date3array = explode("-", $date3);
    $year = $date3array[0];
    $month = $date3array[1];
    $day = $date3array[2];

    $this->set(compact('date3')); 

        // AEQ Large Cap
        include ("Component/connection.php");
        $sql = "SELECT OneMonthReturn, ThreeMonthReturn, OneYearReturn, TwoYearReturn, ThreeYearReturn, FiveYearReturn, SinceInceptionReturn FROM modelportfolios WHERE portfolioname = '"."AEQ Large Cap"."' and year(date)=year('".$date3."') and month(date)=month('".$date3."')"; 
        $sql."<br>";
        $temp=mysqli_query($con,$sql);
        $result=array();
        foreach($temp as $tp)
        {
            foreach($tp as $p)
            {
                array_push($result,$p);
            }
        }

        $sql = "SELECT OneMonthReturn, ThreeMonthReturn, OneYearReturn, TwoYearReturn, ThreeYearReturn, FiveYearReturn, SinceInceptionReturn FROM indices WHERE indexname = '"."S&P/ASX 300 TR"."' and year(date)=year('".$date3."') and month(date)=month('".$date3."')";
        $sql;
        $temp=mysqli_query($con,$sql);
        $result2=array();
        foreach($temp as $tp)
        {
            foreach($tp as $t)
            {
                array_push($result2,$t);
            }
        }

        $result3=array();


        for($n=0;$n<sizeof($result2);$n++)
        {
            $temp=$result[$n]-$result2[$n];
            array_push($result3,$temp);
        }

        $this->set('result',$result);
        $this->set('result2',$result2);
        $this->set('result3',$result3);

        // AEQ Small Cap
        $sql = "SELECT OneMonthReturn, ThreeMonthReturn, OneYearReturn, TwoYearReturn, ThreeYearReturn, FiveYearReturn, SinceInceptionReturn FROM modelportfolios WHERE portfolioname = '"."AEQ Small / Mid Cap"."' and year(date)=year('".$date3."') and month(date)=month('".$date3."')"; 
        $temp=mysqli_query($con,$sql);
        $result4=array();
        foreach($temp as $tp)
        {
            foreach($tp as $t)
            {
                array_push($result4,$t);
            }
        }

        $sql = "SELECT OneMonthReturn, ThreeMonthReturn, OneYearReturn, TwoYearReturn, ThreeYearReturn, FiveYearReturn, SinceInceptionReturn FROM indices WHERE indexname = '"."S&P/ASX Small Ordinaries TR AUD"."' and year(date)=year('".$date3."') and month(date)=month('".$date3."')"; 
        $temp=mysqli_query($con,$sql);
        $result5=array();
        foreach($temp as $tp)
        {
            foreach($tp as $t)
            {
                array_push($result5,$t);
            }
        }

        $result6=array();


        for($n=0;$n<sizeof($result5);$n++)
        {
            $temp=$result4[$n]-$result5[$n];
            array_push($result6,$temp);
        }

        $this->set('result4',$result4);
        $this->set('result5',$result5);
        $this->set('result6',$result6);

        //IEQ
        $sql = "SELECT OneMonthReturn, ThreeMonthReturn, OneYearReturn, TwoYearReturn, ThreeYearReturn, FiveYearReturn, SinceInceptionReturn FROM modelportfolios WHERE portfolioname = '"."IEQ"."' and year(date)=year('".$date3."') and month(date)=month('".$date3."')"; 
        $temp=mysqli_query($con,$sql);
        $result7=array();
        foreach($temp as $tp)
        {
            foreach($tp as $p)
            {
                array_push($result7,$p);
            }
        }

        $sql = "SELECT OneMonthReturn, ThreeMonthReturn, OneYearReturn, TwoYearReturn, ThreeYearReturn, FiveYearReturn, SinceInceptionReturn FROM indices WHERE indexname = '"."MSCI World Ex Australia NR AUD"."' and year(date)=year('".$date3."') and month(date)=month('".$date3."')"; 
        $temp=mysqli_query($con,$sql);
        $result8=array();
        foreach($temp as $tp)
        {
            foreach($tp as $t)
            {
                array_push($result8,$t);
            }
        }

        $result9=array();

        for($n=0;$n<sizeof($result8);$n++)
        {
            $temp=$result7[$n]-$result8[$n];
            array_push($result9,$temp);
        }

        $this->set('result7',$result7);
        $this->set('result8',$result8);
        $this->set('result9',$result9);

        //IEQ Asia
        $sql = "SELECT OneMonthReturn, ThreeMonthReturn, OneYearReturn, TwoYearReturn, ThreeYearReturn, FiveYearReturn, SinceInceptionReturn FROM modelportfolios WHERE portfolioname = '"."IEQ Asia"."' and year(date)=year('".$date3."') and month(date)=month('".$date3."')"; 
        $temp=mysqli_query($con,$sql);
        $result10=array();
        foreach($temp as $tp)
        {
            foreach($tp as $p)
            {
                array_push($result10,$p);
            }
        }

        $sql = "SELECT OneMonthReturn, ThreeMonthReturn, OneYearReturn, TwoYearReturn, ThreeYearReturn, FiveYearReturn, SinceInceptionReturn FROM indices WHERE indexname = '"."MSCI AC Asia Ex Japan NR AUD"."' and year(date)=year('".$date3."') and month(date)=month('".$date3."')"; 
        $temp=mysqli_query($con,$sql);
        $result11=array();
        foreach($temp as $tp)
        {
            foreach($tp as $t)
            {
                array_push($result11,$t);
            }
        }

        $result12=array();


        for($n=0;$n<sizeof($result11);$n++)
        {
            $temp=$result[$n]-$result11[$n];
            array_push($result12,$temp);
        }

        $this->set('result10',$result10);
        $this->set('result11',$result11);
        $this->set('result12',$result12);

        //5
        $sql = "SELECT OneMonthReturn, ThreeMonthReturn, OneYearReturn, TwoYearReturn, ThreeYearReturn, FiveYearReturn, SinceInceptionReturn FROM modelportfolios WHERE portfolioname = '"."Global REITS"."' and year(date)=year('".$date3."') and month(date)=month('".$date3."')"; 
        $temp=mysqli_query($con,$sql);
        $result13=array();
        foreach($temp as $tp)
        {
            foreach($tp as $p)
            {
                array_push($result13,$p);
            }
        }

        $sql = "SELECT OneMonthReturn, ThreeMonthReturn, OneYearReturn, TwoYearReturn, ThreeYearReturn, FiveYearReturn, SinceInceptionReturn FROM indices WHERE indexname = '"."UBS Global Investors NR Hdg AUD"."' and year(date)=year('".$date3."') and month(date)=month('".$date3."')"; 
        $temp=mysqli_query($con,$sql);
        $result14=array();
        foreach($temp as $tp)
        {
            foreach($tp as $t)
            {
                array_push($result14,$t);
            }
        }

        $result15=array();


        for($n=0;$n<sizeof($result14);$n++)
        {
            $temp=$result13[$n]-$result14[$n];
            array_push($result15,$temp);
        }

        $this->set('result13',$result13);
        $this->set('result14',$result14);
        $this->set('result15',$result15);

        //Australian REITS

        $sql = "SELECT OneMonthReturn, ThreeMonthReturn, OneYearReturn, TwoYearReturn, ThreeYearReturn, FiveYearReturn, SinceInceptionReturn FROM modelportfolios WHERE portfolioname = '"."Australian REITS"."' and year(date)=year('".$date3."') and month(date)=month('".$date3."')"; 
        $temp=mysqli_query($con,$sql);
        $result16=array();
        foreach($temp as $tp)
        {
            foreach($tp as $p)
            {
                array_push($result16,$p);
            }
        }

        $sql = "SELECT OneMonthReturn, ThreeMonthReturn, OneYearReturn, TwoYearReturn, ThreeYearReturn, FiveYearReturn, SinceInceptionReturn FROM indices WHERE indexname = '"."S&P/ASX 300 A-REIT TR"."' and year(date)=year('".$date3."') and month(date)=month('".$date3."')"; 
        $temp=mysqli_query($con,$sql);
        $result17=array();
        foreach($temp as $tp)
        {
            foreach($tp as $t)
            {
                array_push($result17,$t);
            }
        }

        $result18=array();


        for($n=0;$n<sizeof($result17);$n++)
        {
            $temp=$result16[$n]-$result17[$n];
            array_push($result18,$temp);
        }

        $this->set('result16',$result16);
        $this->set('result17',$result17);
        $this->set('result18',$result18);

        //Global Infrastructure
        $sql = "SELECT OneMonthReturn, ThreeMonthReturn, OneYearReturn, TwoYearReturn, ThreeYearReturn, FiveYearReturn, SinceInceptionReturn FROM modelportfolios WHERE portfolioname = '"."Global Infrastructure"."' and year(date)=year('".$date3."') and month(date)=month('".$date3."')"; 
        $temp=mysqli_query($con,$sql);
        $result19=array();
        foreach($temp as $tp)
        {
            foreach($tp as $p)
            {
                array_push($result19,$p);
            }
        }

        $sql = "SELECT OneMonthReturn, ThreeMonthReturn, OneYearReturn, TwoYearReturn, ThreeYearReturn, FiveYearReturn, SinceInceptionReturn FROM indices WHERE indexname = '"."UBS G INFRA & UTIL 50-50 HEDGED AUD - TOT RETURN IND"."' and year(date)=year('".$date3."') and month(date)=month('".$date3."')"; 
        $temp=mysqli_query($con,$sql);
        $result20=array();
        foreach($temp as $tp)
        {
            foreach($tp as $t)
            {
                array_push($result20,$t);
            }
        }

        $result21=array();


        for($n=0;$n<sizeof($result20);$n++)
        {
            $temp=$result19[$n]-$result20[$n];
            array_push($result21,$temp);
        }

        $this->set('result19',$result19);
        $this->set('result20',$result20);
        $this->set('result21',$result21);

        //Alternative Investment Strategies Portfolio A
        $sql = "SELECT OneMonthReturn, ThreeMonthReturn, OneYearReturn, TwoYearReturn, ThreeYearReturn, FiveYearReturn, SinceInceptionReturn FROM modelportfolios WHERE portfolioname = '"."AIS-A"."' and year(date)=year('".$date3."') and month(date)=month('".$date3."')"; 
        $temp=mysqli_query($con,$sql);
        $result22=array();
        foreach($temp as $tp)
        {
            foreach($tp as $p)
            {
                array_push($result22,$p);
            }
        }

        $sql = "SELECT OneMonthReturn, ThreeMonthReturn, OneYearReturn, TwoYearReturn, ThreeYearReturn, FiveYearReturn, SinceInceptionReturn FROM indices WHERE indexname = '"."RBA Bank accepted Bills 90 Days"."' and year(date)=year('".$date3."') and month(date)=month('".$date3."')"; 
        $temp=mysqli_query($con,$sql);
        $result23=array();
        foreach($temp as $tp)
        {
            foreach($tp as $t)
            {
                array_push($result23,$t);
            }
        }

        $result24=array();


        for($n=0;$n<sizeof($result23);$n++)
        {
            $temp=$result22[$n]-$result23[$n];
            array_push($result24,$temp);
        }

        $this->set('result22',$result22);
        $this->set('result23',$result23);
        $this->set('result24',$result24);

        //Alternative Investment Strategies Portfolio B

        $sql = "SELECT OneMonthReturn, ThreeMonthReturn, OneYearReturn, TwoYearReturn, ThreeYearReturn, FiveYearReturn, SinceInceptionReturn FROM modelportfolios WHERE portfolioname = '"."AIS-B"."' and year(date)=year('".$date3."') and month(date)=month('".$date3."')"; 
        $temp=mysqli_query($con,$sql);
        $result25=array();
        foreach($temp as $tp)
        {
            foreach($tp as $p)
            {
                array_push($result25,$p);
            }
        }

        $sql = "SELECT OneMonthReturn, ThreeMonthReturn, OneYearReturn, TwoYearReturn, ThreeYearReturn, FiveYearReturn, SinceInceptionReturn FROM indices WHERE indexname = '"."HFRI Macro (Total) Index (USD)"."' and year(date)=year('".$date3."') and month(date)=month('".$date3."')"; 
        $temp=mysqli_query($con,$sql);
        $result26=array();
        foreach($temp as $tp)
        {
            foreach($tp as $t)
            {
                array_push($result26,$t);
            }
        }

        $result27=array();


        for($n=0;$n<sizeof($result26);$n++)
        {
            $temp=$result25[$n]-$result26[$n];
            array_push($result27,$temp);
        }

        $this->set('result25',$result25);
        $this->set('result26',$result26);
        $this->set('result27',$result27);

        //Diversified Credit & Fixed Income Portfolio
        $sql = "SELECT OneMonthReturn, ThreeMonthReturn, OneYearReturn, TwoYearReturn, ThreeYearReturn, FiveYearReturn, SinceInceptionReturn FROM modelportfolios WHERE portfolioname = '"."Diversified Credit & Fixed Income"."' and year(date)=year('".$date3."') and month(date)=month('".$date3."')"; 
        $temp=mysqli_query($con,$sql);
        $result28=array();
        foreach($temp as $tp)
        {
            foreach($tp as $p)
            {
                array_push($result28,$p);
            }
        }

        $sql = "SELECT OneMonthReturn, ThreeMonthReturn, OneYearReturn, TwoYearReturn, ThreeYearReturn, FiveYearReturn, SinceInceptionReturn FROM indices WHERE indexname = '"."Barclays Global Credit Corp TR Hdg AUD"."' and year(date)=year('".$date3."') and month(date)=month('".$date3."')"; 
        $temp=mysqli_query($con,$sql);
        $result29=array();
        foreach($temp as $tp)
        {
            foreach($tp as $t)
            {
                array_push($result29,$t);
            }
        }

        $result30=array();


        for($n=0;$n<sizeof($result29);$n++)
        {
            $temp=$result28[$n]-$result29[$n];
            array_push($result30,$temp);
        }

        $this->set('result28',$result28);
        $this->set('result29',$result29);
        $this->set('result30',$result30);

        //Sovereign Bonds Portfolio
        $sql = "SELECT OneMonthReturn, ThreeMonthReturn, OneYearReturn, TwoYearReturn, ThreeYearReturn, FiveYearReturn, SinceInceptionReturn FROM modelportfolios WHERE portfolioname = '"."Sovereign Bonds"."' and year(date)=year('".$date3."') and month(date)=month('".$date3."')"; 
        $temp=mysqli_query($con,$sql);
        $result31=array();
        foreach($temp as $tp)
        {
            foreach($tp as $p)
            {
                array_push($result31,$p);
            }
        }

        $sql = "SELECT OneMonthReturn, ThreeMonthReturn, OneYearReturn, TwoYearReturn, ThreeYearReturn, FiveYearReturn, SinceInceptionReturn FROM indices WHERE indexname = '"."UBS Composite 0+ Yr TR AUD"."' and year(date)=year('".$date3."') and month(date)=month('".$date3."')"; 
        $temp=mysqli_query($con,$sql);
        $result32=array();
        foreach($temp as $tp)
        {
            foreach($tp as $t)
            {
                array_push($result32,$t);
            }
        }

        $result33=array();


        for($n=0;$n<sizeof($result32);$n++)
        {
            $temp=$result31[$n]-$result32[$n];
            array_push($result33,$temp);
        }

        $this->set('result31',$result31);
        $this->set('result32',$result32);
        $this->set('result33',$result33);

        //Enhanced Cash
        $sql = "SELECT OneMonthReturn, ThreeMonthReturn, OneYearReturn, TwoYearReturn, ThreeYearReturn, FiveYearReturn, SinceInceptionReturn FROM modelportfolios WHERE portfolioname = '"."Enhanced Cash"."' and year(date)=year('".$date3."') and month(date)=month('".$date3."')"; 
        $temp=mysqli_query($con,$sql);
        $result34=array();
        foreach($temp as $tp)
        {
            foreach ($tp as $t)
            {
                array_push($result34,$t);
            }
        }

        $sql = "SELECT OneMonthReturn, ThreeMonthReturn, OneYearReturn, TwoYearReturn, ThreeYearReturn, FiveYearReturn, SinceInceptionReturn FROM indices WHERE indexname = '"."RBA Bank accepted Bills 90 Days"."' and year(date)=year('".$date3."') and month(date)=month('".$date3."')"; 
        $temp=mysqli_query($con,$sql);
        $result35=array();
        foreach($temp as $tp)
        {
            foreach($tp as $t)
            {
                array_push($result35,$t);
            }
        }

        $result36=array();


        for($n=0;$n<sizeof($result35);$n++)
        {
            $temp=$result[$n]-$result35[$n];
            array_push($result36,$temp);
        }

        $this->set('result34',$result34);
        $this->set('result35',$result35);
        $this->set('result36',$result36);

        //Cash Portfolio
        $sql = "SELECT OneMonthReturn, ThreeMonthReturn, OneYearReturn, TwoYearReturn, ThreeYearReturn, FiveYearReturn, SinceInceptionReturn FROM modelportfolios WHERE portfolioname = '"."Cash"."' and year(date)=year('".$date3."') and month(date)=month('".$date3."')"; 
        $temp=mysqli_query($con,$sql);
        $result37=array();
        foreach($temp as $tp)
        {
            foreach($tp as $p)
            {
                array_push($result37,$p);
            }
        }

        $sql = "SELECT OneMonthReturn, ThreeMonthReturn, OneYearReturn, TwoYearReturn, ThreeYearReturn, FiveYearReturn, SinceInceptionReturn FROM indices WHERE indexname = '"."RBA Bank accepted Bills 90 Days"."' and year(date)=year('".$date3."') and month(date)=month('".$date3."')"; 
        $temp=mysqli_query($con,$sql);
        $result38=array();
        foreach($temp as $tp)
        {
            foreach($tp as $t)
            {
                array_push($result38,$t);
            }
        }

        $result39=array();


        for($n=0;$n<sizeof($result38);$n++)
        {
            $temp=$result37[$n]-$result38[$n];
            array_push($result39,$temp);
        }

        $this->set('result37',$result37);
        $this->set('result38',$result38);
        $this->set('result39',$result39);

        //RPM Conservative Portfolio
        $sql = "SELECT OneMonthReturn, ThreeMonthReturn, OneYearReturn, TwoYearReturn, ThreeYearReturn, FiveYearReturn, SinceInceptionReturn FROM modelportfolios WHERE portfolioname = '"."RPM - Conservative"."' and year(date)=year('".$date3."') and month(date)=month('".$date3."')"; 
        $temp=mysqli_query($con,$sql);
        $result40=array();
        foreach($temp as $tp)
        {
            foreach($tp as $t)
            {
                array_push($result40,$tp);
            }
        }

        $sql = "SELECT OneMonthReturn, ThreeMonthReturn, OneYearReturn, TwoYearReturn, ThreeYearReturn, FiveYearReturn, SinceInceptionReturn FROM indices WHERE indexname = '"."Mstar PG ITr Multisector Conserv Idx"."' and year(date)=year('".$date3."') and month(date)=month('".$date3."')"; 
        $temp=mysqli_query($con,$sql);
        $result41=array();
        foreach($temp as $tp)
        {
            foreach($tp as $t)
            {
                array_push($result41,$t);
            }
        }

        $result42=array();

        //delete after db have index 
        if(sizeof($result40)==0)
        {
            for($m=0;$m<sizeof($result41);$m++)
            {
                array_push($result40,0);
            }
        }
        ////////////


        for($n=0;$n<sizeof($result41);$n++)
        {
            $temp=$result40[$n]-$result41[$n];
            array_push($result42,$temp);
        }

        $this->set('result40',$result40);
        $this->set('result41',$result41);
        $this->set('result42',$result42);

        //RPM Balanced Portfolio

        $sql = "SELECT OneMonthReturn, ThreeMonthReturn, OneYearReturn, TwoYearReturn, ThreeYearReturn, FiveYearReturn, SinceInceptionReturn FROM modelportfolios WHERE portfolioname = '"."RPM - Balanced"."' and year(date)=year('".$date3."') and month(date)=month('".$date3."')"; 
        $temp=mysqli_query($con,$sql);
        $result43=array();
        foreach($temp as $tp)
        {
            foreach($tp as $t)
            {
                array_push($result43,$tp);
            }
        }

        $sql = "SELECT OneMonthReturn, ThreeMonthReturn, OneYearReturn, TwoYearReturn, ThreeYearReturn, FiveYearReturn, SinceInceptionReturn FROM indices WHERE indexname = '"."Mstar PG Itr Multisector Balanced Idx"."' and year(date)=year('".$date3."') and month(date)=month('".$date3."')"; 
        $temp=mysqli_query($con,$sql);
        $result44=array();
        foreach($temp as $tp)
        {
            foreach($tp as $t)
            {
                array_push($result44,$t);
            }
        }

        $result45=array();

        //delete after db have index 
        if(sizeof($result43)==0)
        {
            for($m=0;$m<sizeof($result44);$m++)
            {
                array_push($result43,0);
            }
        }
        ////////////


        for($n=0;$n<sizeof($result44);$n++)
        {
            $temp=$result43[$n]-$result44[$n];
            array_push($result45,$temp);
        }

        $this->set('result43',$result43);
        $this->set('result44',$result44);
        $this->set('result45',$result45);

        //RPM High Growth Portfolio

        $sql = "SELECT OneMonthReturn, ThreeMonthReturn, OneYearReturn, TwoYearReturn, ThreeYearReturn, FiveYearReturn, SinceInceptionReturn FROM modelportfolios WHERE portfolioname = '"."RPM - High Growth"."' and year(date)=year('".$date3."') and month(date)=month('".$date3."')"; 
        $temp=mysqli_query($con,$sql);
        $result46=array();
        foreach($temp as $tp)
        {
            foreach($tp as $t)
            {
                array_push($result46,$t);   
            }
        }

        $sql = "SELECT OneMonthReturn, ThreeMonthReturn, OneYearReturn, TwoYearReturn, ThreeYearReturn, FiveYearReturn, SinceInceptionReturn FROM indices WHERE indexname = '"."Mstar PG ITr Multisector Aggressive Idx"."' and year(date)=year('".$date3."') and month(date)=month('".$date3."')"; 
        $temp=mysqli_query($con,$sql);
        $result47=array();
        foreach($temp as $tp)
        {
            foreach($tp as $t)
            {
                array_push($result47,$t);
            }
        }

        $result48=array();

        //delete after db have index 
        if(sizeof($result46)==0)
        {
            for($m=0;$m<sizeof($result47);$m++)
            {
                array_push($result46,0);
            }
        }
        ////////////


        for($n=0;$n<sizeof($result47);$n++)
        {
            $temp=$result46[$n]-$result47[$n];
            array_push($result48,$temp);
        }

        $this->set('result46',$result46);
        $this->set('result47',$result47);
        $this->set('result48',$result48);
    }

private function convertpercent($original)
{
    $returntemp=$original*100;
    $return=(string)$returntemp."%";
    return $return;
}

}